using DesktopWidgetApp.Shared;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace DesktopWidgetApp.Core.Data
{
    /// <summary>
    /// SQLite状态变更历史仓储实现
    /// </summary>
    public class SqliteStatusChangeHistoryRepository : SqliteRepositoryBase<StatusChangeHistory>, IStatusChangeHistoryRepository
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public SqliteStatusChangeHistoryRepository(string connectionString) : base(connectionString)
        {
        }

        /// <summary>
        /// 获取指定用户的状态变更历史
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="limit">获取的记录数量限制</param>
        /// <returns>状态变更历史记录列表</returns>
        public async Task<IEnumerable<StatusChangeHistory>> GetByUserIdAsync(Guid userId, int limit = 50)
        {
            using (var connection = await OpenConnectionAsync())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM StatusChangeHistory WHERE UserId = @UserId ORDER BY ChangeTime DESC LIMIT @Limit";
                    command.Parameters.AddWithValue("@UserId", userId.ToString());
                    command.Parameters.AddWithValue("@Limit", limit);

                    var results = new List<StatusChangeHistory>();
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            results.Add(MapToEntity(reader));
                        }
                    }
                    return results;
                }
            }
        }

        /// <summary>
        /// 获取指定状态的变更历史
        /// </summary>
        /// <param name="statusId">状态ID</param>
        /// <returns>状态变更历史记录列表</returns>
        public async Task<IEnumerable<StatusChangeHistory>> GetByStatusIdAsync(Guid statusId)
        {
            using (var connection = await OpenConnectionAsync())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM StatusChangeHistory WHERE StatusId = @StatusId ORDER BY ChangeTime DESC";
                    command.Parameters.AddWithValue("@StatusId", statusId.ToString());

                    var results = new List<StatusChangeHistory>();
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            results.Add(MapToEntity(reader));
                        }
                    }
                    return results;
                }
            }
        }

        /// <summary>
        /// 获取指定时间范围内的状态变更历史
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>状态变更历史记录列表</returns>
        public async Task<IEnumerable<StatusChangeHistory>> GetByTimeRangeAsync(DateTime startTime, DateTime endTime)
        {
            using (var connection = await OpenConnectionAsync())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM StatusChangeHistory WHERE ChangeTime BETWEEN @StartTime AND @EndTime ORDER BY ChangeTime DESC";
                    command.Parameters.AddWithValue("@StartTime", startTime.ToString("yyyy-MM-dd HH:mm:ss"));
                    command.Parameters.AddWithValue("@EndTime", endTime.ToString("yyyy-MM-dd HH:mm:ss"));

                    var results = new List<StatusChangeHistory>();
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            results.Add(MapToEntity(reader));
                        }
                    }
                    return results;
                }
            }
        }

        /// <summary>
        /// 将数据库记录映射到实体对象
        /// </summary>
        /// <param name="reader">数据读取器</param>
        /// <returns>状态变更历史实体</returns>
        protected override StatusChangeHistory MapToEntity(SqliteDataReader reader)
        {
            return new StatusChangeHistory
            {
                Id = Guid.Parse(reader["Id"].ToString()),
                UserId = Guid.Parse(reader["UserId"].ToString()),
                StatusId = Guid.Parse(reader["StatusId"].ToString()),
                PreviousStatusType = (StatusType)Enum.Parse(typeof(StatusType), reader["PreviousStatusType"].ToString()),
                NewStatusType = (StatusType)Enum.Parse(typeof(StatusType), reader["NewStatusType"].ToString()),
                ChangeTime = DateTime.Parse(reader["ChangeTime"].ToString()),
                ChangeReason = reader["ChangeReason"] != DBNull.Value ? reader["ChangeReason"].ToString() : null
            };
        }

        /// <summary>
        /// 将实体对象映射到数据库参数
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <param name="entity">状态变更历史实体</param>
        protected override void MapToParameters(SqliteCommand command, StatusChangeHistory entity)
        {
            command.Parameters.AddWithValue("@Id", entity.Id.ToString());
            command.Parameters.AddWithValue("@UserId", entity.UserId.ToString());
            command.Parameters.AddWithValue("@StatusId", entity.StatusId.ToString());
            command.Parameters.AddWithValue("@PreviousStatusType", (int)entity.PreviousStatusType);
            command.Parameters.AddWithValue("@NewStatusType", (int)entity.NewStatusType);
            command.Parameters.AddWithValue("@ChangeTime", entity.ChangeTime.ToString("yyyy-MM-dd HH:mm:ss"));
            command.Parameters.AddWithValue("@ChangeReason", entity.ChangeReason ?? DBNull.Value);
        }
    }
}